package com.telecom.sxint.app.api.controller.EngineerController;
import cn.hutool.core.util.StrUtil;
import cn.zjtele.pubinfo.boot.redis.config.RedisHelper;
import cn.zjtele.pubinfo.common.http.response.ResponseData;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.telecom.sxint.app.core.domain.entity.Engineerpojo.App1Employeehours;
import com.telecom.sxint.app.core.domain.entity.Engineerpojo.App1Engineercertification;
import com.telecom.sxint.app.core.domain.entity.Engineerpojo.App1Examschedule;
import com.telecom.sxint.app.core.service.Engineerservice.IApp1EmployeehoursService;
import com.telecom.sxint.app.core.service.Engineerservice.IApp1EngineercertificationService;
import com.telecom.sxint.app.core.service.Engineerservice.IApp1ExamscheduleService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.RequiredArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.cache.CacheProperties;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.text.ParseException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * @author zhulintao
 * @version 2.0
 * @date 2024/8/21 15:58
 */
@RestController
@RequestMapping("/app1-engineer")
@Tag(name = "导入控制器", description = "信息导入管理")
@RequiredArgsConstructor
public class EngineerFileUpload {
    @Autowired
    private IApp1ExamscheduleService iapp1ExamscheduleService;
    @Autowired
    private IApp1EngineercertificationService iApp1EngineercertificationService;
    @Autowired
    private IApp1EmployeehoursService iApp1EmployeehoursService;
    @Autowired
    private RedisHelper redisHelper;
    String getCellValue(Cell cell) {
        if (cell != null) {
            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    return String.valueOf(cell.getNumericCellValue());
                case BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                default:
                    return "";
            }
        }
        return "";
    }
    Integer getCellValueInteger(String string){
        int i = 0;
        if(!string.isEmpty()){
            double doubleValue = Double.parseDouble(string);
            int intValue = (int) doubleValue;
            return intValue;
        }
        return i;
    }
    // 辅助方法，用于从单元格中获取整数值
    private Integer getCellValueInteger(Cell cell) {
        if (cell == null) {
            return null;
        }
        return (int) cell.getNumericCellValue();
    }
    public String matcher(String str){
        if(StrUtil.isBlank(str)){
            return "";
        }
        String[] parts = str.split("月");
        String month = parts[0];
        String day = parts[1].replaceAll("[^\\d]", ""); // 移除非数字字符

        // 补零以确保月份和日期都是两位数
        if (month.length() == 1) {
            month = "0" + month;
        }
        if (day.length() == 1) {
            day = "0" + day;
        }

        // 拼接成MMDD格式
        return month + day;
    }

    /**
     * 导入文件
     * @param file
     * @return
     */
    @Operation(summary = "考情导入excel文档录入数据库", description = "返回测试信息表分页")
    @PostMapping("/examFileupload")
    public ResponseData<List<App1Examschedule>>  handleexamFileUpload(@RequestParam("file") MultipartFile file) {
        redisHelper.deletePattern("App1ExamRegistrationUser:*");
        //导入前清空数据库
        iapp1ExamscheduleService.deleteAll();

        if (file.isEmpty()) {
            return ResponseData.failed("没有文件","4101");
        }
        try (InputStream inputStream = file.getInputStream();

             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                // 工作表为空，返回错误信息或空列表
                return ResponseData.failed("没有可读取的工作表","4100");
            }

            List<App1Examschedule> app1= new ArrayList<>();
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // Skip header row if present

                App1Examschedule log = new App1Examschedule();
                int id = 0;
                if (row.getCell(0) != null) {
                    // 检查单元格是否包含一个数字
                    if (row.getCell(0).getCellType() == CellType.NUMERIC) {
                        // 将单元格的数值转换为整数
                        id = (int) row.getCell(0).getNumericCellValue();
                    } else {
                        // 单元格不是数值类型，根据需要处理错误或返回默认值
                    }
                }

                System.out.println("id:" + id);
                System.out.println(getCellValue(row.getCell(1)));
                log.setExamLevel(getCellValue(row.getCell(0)));

                log.setExamDate(matcher(getCellValue(row.getCell(1))));
                log.setRegistrationStartDate(matcher(getCellValue(row.getCell(2))));
                log.setRegistrationDeadline(matcher(getCellValue(row.getCell(3))));


                log.setDescription(getCellValue(row.getCell(4)));

                app1.add(log);


            }
            iapp1ExamscheduleService.saveBatch(app1);
            return ResponseData.success(app1);

        } catch (IOException e) {
            return ResponseData.failed("导入失败","4102");
        }
    }
    /**
     * 导入工时文件
     * @param file
     * @return
     */
    @Operation(summary = "工时导入excel文档录入数据库", description = "返回工时信息表")
    @PostMapping("/EmployeeHoursFileupload")
    @Transactional
    public ResponseData<List<App1Employeehours>> handleEmployeeHoursUpload(@RequestParam("file") MultipartFile file) {
        redisHelper.deletePattern("employee_hours*");
        if (file.isEmpty()) {
            return ResponseData.failed("没有文件","4101");
        }
//判断为xls或xlsx文件
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            return ResponseData.failed("文件格式错误","4103");
        }
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                // 工作表为空，返回错误信息或空列表
                return ResponseData.failed("没有可读取的工作表","4100");
            }
            if(!sheet.getRow(0).getCell(0).getStringCellValue().equals("工时月份") ||
                    !sheet.getRow(0).getCell(1).getStringCellValue().equals("成员姓名")||
                    !sheet.getRow(0).getCell(2).getStringCellValue().equals("成员ID") ||
                    !sheet.getRow(0).getCell(3).getStringCellValue().equals("复核工时（天）")) {
                return ResponseData.failed("文件格式错误","4103");
            }
            List<App1Employeehours> app1= new ArrayList<>();
            DataFormatter dataFormatter=new DataFormatter();
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // Skip header row if present

                App1Employeehours log = new App1Employeehours();
                if(StrUtil.isBlank(getCellValue(row.getCell(0)))||StrUtil.isBlank(getCellValue(row.getCell(1)))||
                        StrUtil.isBlank(getCellValue(row.getCell(2)))||StrUtil.isBlank(getCellValue(row.getCell(3)))){
                    continue;
                }

                log.setWorkMonth(getCellValue(row.getCell(0)));
                log.setEmployeeName(getCellValue(row.getCell(1)));
                log.setEmployeeId( dataFormatter.formatCellValue(row.getCell(2)));
                log.setReviewedHours(Double.parseDouble(getCellValue(row.getCell(3))));
                app1.add(log);
            }
            iApp1EmployeehoursService.saveBatch(app1);
            return ResponseData.success(app1);

        } catch (IOException e) {
            return ResponseData.failed("导入失败","4102");
        }
    }
    /**
     * 清除缓存
     * @param
     * @return
     */
    @Operation(summary = "清除缓存", description = "清除缓存")
    @PostMapping("/clearRedis")
    public ResponseData clearRedis() {
        redisHelper.deletePattern("*");
        return ResponseData.success("清除成功");
    }
    /**
     * 导入工程师文件
     * @param file
     * @return
     */
    @Operation(summary = "工程师导入excel文档录入数据库", description = "返回测试信息表分页")
    @PostMapping("/CertificationFileupload")
    @Transactional
    public ResponseData<List<App1Engineercertification>>  handleFileUpload(@RequestParam("file") MultipartFile file) {
        redisHelper.deletePattern("engineer_certification*");
        if (file.isEmpty()) {
            return ResponseData.failed("没有文件","4101");
        }
        String fileName = file.getOriginalFilename();
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
            return ResponseData.failed("文件格式错误","4103");
        }
        try (InputStream inputStream = file.getInputStream();

             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                // 工作表为空，返回错误信息或空列表
                return ResponseData.failed("没有可读取的工作表","4100");
            }

            List<App1Engineercertification> app1= new ArrayList<>();
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // Skip header row if present

                App1Engineercertification log = new App1Engineercertification();
                int id = 0;
                if (row.getCell(0) != null) {
                    // 检查单元格是否包含一个数字
                    if (row.getCell(0).getCellType() == CellType.NUMERIC) {
                        // 将单元格的数值转换为整数
                        id = (int) row.getCell(0).getNumericCellValue();
                    } else {
                        // 单元格不是数值类型，根据需要处理错误或返回默认值
                    }
                }
                DataFormatter dataFormatter = new DataFormatter();
                log.setCompany(getCellValue(row.getCell(0)));
                log.setPersonnelCode(dataFormatter.formatCellValue(row.getCell(1)));
                log.setName(getCellValue(row.getCell(2)));
                log.setIsRdCloud(getCellValueInteger(getCellValue(row.getCell(3))));
                log.setIsRdBase(getCellValueInteger(getCellValue(row.getCell(4))));
                log.setIsRdCommon(getCellValueInteger(getCellValue(row.getCell(5))));
                log.setRdCommonLevel(getCellValue(row.getCell(6)));
                log.setIsRdProfession(getCellValueInteger(getCellValue(row.getCell(7))));
                log.setRdProfessionLevel(getCellValue(row.getCell(8)));
                log.setRdExperienceLevel(getCellValue(row.getCell(9)));
                //定义集合匹配研发工程师认证级别
                List<Integer> minNum=new ArrayList<>();
                for(int i =0; i<=4;i++){
                    if (i==1){
                        continue;
                    }
                    String str=getCellValue(row.getCell(i+6));
                    if (!str.isEmpty()){
                        char lastChar = str.charAt(str.length() - 1); // 获取最后一个字符
                        int lastCharAsInt = Character.getNumericValue(lastChar);
                        minNum.add(lastCharAsInt);
                    }
                }
                if (minNum.size()==3){
                    log.setEngineerLevel("研发工程师L"+ Collections.min(minNum));
                }
                else {
                    log.setEngineerLevel("");
                }
                // 处理日志和保存操作
                app1.add(log);
            }
            iApp1EngineercertificationService.remove(new QueryWrapper<>());
            iApp1EngineercertificationService.saveBatch(app1);
            return ResponseData.success(app1);

        } catch (IOException e) {
            return ResponseData.failed("导入失败","4102");
        }
    }
}
